package com.etc.traval.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.traval.dao.DiaryDao;
import com.etc.traval.dao.DiarycomDao;
import com.etc.traval.dao.UserDao;
import com.etc.traval.util.JDBCUtil;
import com.etc.travel.entity.Diary;
import com.etc.travel.entity.Diarycom;
import com.etc.travel.entity.User;

import oracle.jdbc.internal.OracleTypes;

public class DiarycomDaoImpl implements DiarycomDao {

	JDBCUtil util = new JDBCUtil();
	DiaryDao dao = new DiaryDaoImpl();
	UserDao dao1 = new UserDaoImpl();

	/**
	 * 通过用户查找游记评论
	 * 
	 * @author 刘一清
	 * @param 传入一个user对象
	 * @return 返回查找到的Diarycom对象集合
	 * @date 2018年5月30日10时05分46秒
	 */
	@Override
	public List<Diarycom> queryDiarycomByUser(User user, int page, int size) {
		ResultSet rs =util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_diarycom where diarycom_user=?) s where rownum<=?) t where t.a>?",
				user.getUser_id(), page * size, (page - 1) * size);
		List<Diarycom> list = new ArrayList<Diarycom>();
		try {
			while (rs.next()) {
				Diarycom diarycom = new Diarycom();
				diarycom.setDiartcom_id(rs.getInt("diarycom_id"));
				diarycom.setDiarycom_content(rs.getString("diarycom_content"));
				diarycom.setDiarycom_diary(dao.queryDiaryById(rs.getInt("diarycom_diary")));
				diarycom.setDiarycom_time(rs.getTimestamp("diarycom_time"));
				diarycom.setDiarycom_user(dao1.queryUserById(rs.getInt("diarycom_user")));
				list.add(diarycom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	/**
	 * 通过id查找游记评论
	 * 
	 * @author 刘一清
	 * @param 传入要查找的游记评论id
	 * @return 返回查找到的Diarycom对象
	 * @date 2018年5月30日10时11分27秒
	 */
	@Override
	public Diarycom queryDiarycomById(int id) {
		ResultSet rs = util.query("select * from t_diarycom where diarycom_id=?", id);
		Diarycom diarycom = new Diarycom();
		try {
			if (rs.next()) {
				diarycom.setDiartcom_id(rs.getInt("diarycom_id"));
				diarycom.setDiarycom_content(rs.getString("diarycom_content"));
				diarycom.setDiarycom_diary(dao.queryDiaryById(rs.getInt("diarycom_diary")));
				diarycom.setDiarycom_time(rs.getTimestamp("diarycom_time"));
				diarycom.setDiarycom_user(dao1.queryUserById(rs.getInt("diarycom_user")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return diarycom;
	}

	/**
	 * 通过游记名查找游记评论
	 * 
	 * @author 刘一清
	 * @param 传入一个diary对象
	 * @return 返回查找到的Diarycom对象集合
	 * @date 2018年5月30日10时16分
	 */
	@Override
	public List<Diarycom> queryDiarycomByDiary(Diary diary, int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_diarycom where diarycom_diary=?) s where rownum<=?) t where t.a>?",
				diary.getDiary_id(), page * size, (page - 1) * size);
		List<Diarycom> list = new ArrayList<Diarycom>();
		try {
			while (rs.next()) {
				Diarycom diarycom = new Diarycom();
				diarycom.setDiartcom_id(rs.getInt("diarycom_id"));
				diarycom.setDiarycom_content(rs.getString("diarycom_content"));
				diarycom.setDiarycom_diary(dao.queryDiaryById(rs.getInt("diarycom_diary")));
				diarycom.setDiarycom_time(rs.getTimestamp("diarycom_time"));
				diarycom.setDiarycom_user(dao1.queryUserById(rs.getInt("diarycom_user")));
				list.add(diarycom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	/**
	 * 添加一个游记评论
	 * 
	 * @author 刘一清
	 * @param 传入要添加的diarycom对象
	 * @return 返回是否添加成功
	 * @date 2018年5鱼人30日10时24分30秒
	 */
	@Override
	public boolean addDiarycom(Diarycom diarycom) {
		return util.update(
				"insert into t_diarycom (diarycom_content,diarycom_diary,diarycom_time,diarycom_user) values(?,?,?,?)",
				diarycom.getDiarycom_content(), diarycom.getDiarycom_diary().getDiary_id(), diarycom.getDiarycom_time(),
				diarycom.getDiarycom_user().getUser_id()) > 0;
	}

	/**
	 * 根据id删除游记评论
	 * 
	 * @author 刘一清
	 * @param 传入要删除的id
	 * @return 返回是否删除成功
	 * @date 2018年5月30日10时43分17秒
	 */
	@Override
	public boolean deleteDiartcomById(int id) {
		return util.update("delete from t_diarycom where diarycom_id=?", id) > 0;
	}

	/**
	 * 查询一个游记评论的个数
	 * 
	 * @author 刘一清
	 * @param 传入要查找的diary对象
	 * @return 返回查到的评论总数count
	 * @date 2018年5月31日14时00分19秒
	 */
	@Override
	public int queryCountByDiary(Diary diary) {
		int count = 0;
		ResultSet rs = util.query("select count(*) as countnum from t_diarycom where diarycom_diary=?",
				diary.getDiary_id());
		try {
			if (rs.next()) {
				count = rs.getInt("countnum");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	
	/**
	 * 根据评论数量分页排序评论
	 * @author cjw
	 * @param size   page   条数   页码
	 * @return list2  排序集合
	 */
	@Override
	public List<Diary> queryAllDiaryByCom(int size, int page) {
		List<Diary> list2 = new ArrayList<Diary>();
		List<Integer> list = new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] obj = util.callHasReturn("{call proc_queryAllDiaryByCom(?,?,?)}", list,size,page);
		ResultSet rs = (ResultSet) obj[0];
		DiaryDao dao = new DiaryDaoImpl();
		try {
			while (rs.next()) {
				Diary diary = dao.queryDiaryById(rs.getInt("diarycom_diary"));
				diary.setDiaryComNum(rs.getInt("comnum"));
				list2.add(diary);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				util.closeAll();
			} catch (Exception e2) {
			}
		}		
		return list2;
	}

	
	/**
	 * 根据评论次数为用户排名
	 * @author cjw
	 * @return 
	 */
	@Override
	public List<User> queryUserByCom(int size, int page) {
		List<User> list2 = new ArrayList<User>();
		List<Integer> list = new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] obj = util.callHasReturn("{call proc_queryUserByCom(?,?,?)}", list,size,page);
		ResultSet rs = (ResultSet) obj[0];
		UserDao dao = new UserDaoImpl();
		try {
			while (rs.next()) {
				User user = dao.queryUserById(rs.getInt("diarycom_user"));
				user.setDiaryComNum(rs.getInt("comnum"));
				list2.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				util.closeAll();
			} catch (Exception e2) {
			}
		}		
		return list2;
	}

	/**
	 * 根据游记ID查对应游记的评论
	 * @author cjw
	 * @param id 游记ID
	 * @return 查询到的评论
	 */
	@Override
	public List<Diarycom> queryDiarycomByDiaryId(int id,int size,int page) {
		List<Diarycom> list2 = new ArrayList<Diarycom>();
		List<Integer> list = new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] obj = util.callHasReturn("{call proc_queryDiarycomByDiaryId(?,?,?,?)}", list,id,size,page);
		ResultSet rs = (ResultSet) obj[0];
		try {
			while (rs.next()) {
				Diarycom diarycom = new Diarycom();
				diarycom.setDiartcom_id(rs.getInt("diarycom_id"));
				diarycom.setDiarycom_content(rs.getString("diarycom_content"));
				diarycom.setDiarycom_diary(dao.queryDiaryById(rs.getInt("diarycom_diary")));
				diarycom.setDiarycom_time(rs.getTimestamp("diarycom_time"));
				diarycom.setDiarycom_user(dao1.queryUserById(rs.getInt("diarycom_user")));
				list2.add(diarycom);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				util.closeAll();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return list2;
	}

}
